
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 08/15/2011 13:40:00
-- Generated from EDMX file: E:\ss\SweetSurvey\SweetSurvey\Models\SweetSurveyModel.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [SweetSurvey];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_QuestionOption]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Options] DROP CONSTRAINT [FK_QuestionOption];
GO
IF OBJECT_ID(N'[dbo].[FK_SurveyQuestion]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Questions] DROP CONSTRAINT [FK_SurveyQuestion];
GO
IF OBJECT_ID(N'[dbo].[FK_OptionAnswer]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Answers] DROP CONSTRAINT [FK_OptionAnswer];
GO
IF OBJECT_ID(N'[dbo].[FK_ParticipatorAnswer]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Answers] DROP CONSTRAINT [FK_ParticipatorAnswer];
GO
IF OBJECT_ID(N'[dbo].[FK_EditorSurvey]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[Surveys] DROP CONSTRAINT [FK_EditorSurvey];
GO

-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[Questions]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Questions];
GO
IF OBJECT_ID(N'[dbo].[Options]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Options];
GO
IF OBJECT_ID(N'[dbo].[Surveys]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Surveys];
GO
IF OBJECT_ID(N'[dbo].[Answers]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Answers];
GO
IF OBJECT_ID(N'[dbo].[Editors]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Editors];
GO
IF OBJECT_ID(N'[dbo].[Participators]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Participators];
GO

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Questions'
CREATE TABLE [dbo].[Questions] (
    [Id] nvarchar(16)  NOT NULL,
    [Title] nvarchar(max)  NOT NULL,
    [SurveyId] nvarchar(16)  NOT NULL,
    [Index] smallint  NOT NULL
);
GO

-- Creating table 'Options'
CREATE TABLE [dbo].[Options] (
    [Id] nvarchar(16)  NOT NULL,
    [QuestionId] nvarchar(16)  NOT NULL,
    [Text] nvarchar(max)  NOT NULL,
    [Index] smallint  NOT NULL
);
GO

-- Creating table 'Surveys'
CREATE TABLE [dbo].[Surveys] (
    [Id] nvarchar(16)  NOT NULL,
    [EditorId] nvarchar(256)  NOT NULL,
    [Title] nvarchar(max)  NOT NULL,
    [Published] bit  NOT NULL,
    [DateCreated] datetime  NOT NULL
);
GO

-- Creating table 'Answers'
CREATE TABLE [dbo].[Answers] (
    [Id] nvarchar(16)  NOT NULL,
    [OptionId] nvarchar(16)  NOT NULL,
    [ParticipatorId] nvarchar(16)  NOT NULL
);
GO

-- Creating table 'Editors'
CREATE TABLE [dbo].[Editors] (
    [Id] nvarchar(256)  NOT NULL
);
GO

-- Creating table 'Participators'
CREATE TABLE [dbo].[Participators] (
    [Id] nvarchar(16)  NOT NULL,
    [Date] datetime  NOT NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [Id] in table 'Questions'
ALTER TABLE [dbo].[Questions]
ADD CONSTRAINT [PK_Questions]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Options'
ALTER TABLE [dbo].[Options]
ADD CONSTRAINT [PK_Options]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Surveys'
ALTER TABLE [dbo].[Surveys]
ADD CONSTRAINT [PK_Surveys]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Answers'
ALTER TABLE [dbo].[Answers]
ADD CONSTRAINT [PK_Answers]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Editors'
ALTER TABLE [dbo].[Editors]
ADD CONSTRAINT [PK_Editors]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- Creating primary key on [Id] in table 'Participators'
ALTER TABLE [dbo].[Participators]
ADD CONSTRAINT [PK_Participators]
    PRIMARY KEY CLUSTERED ([Id] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [QuestionId] in table 'Options'
ALTER TABLE [dbo].[Options]
ADD CONSTRAINT [FK_QuestionOption]
    FOREIGN KEY ([QuestionId])
    REFERENCES [dbo].[Questions]
        ([Id])
    ON DELETE CASCADE ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_QuestionOption'
CREATE INDEX [IX_FK_QuestionOption]
ON [dbo].[Options]
    ([QuestionId]);
GO

-- Creating foreign key on [SurveyId] in table 'Questions'
ALTER TABLE [dbo].[Questions]
ADD CONSTRAINT [FK_SurveyQuestion]
    FOREIGN KEY ([SurveyId])
    REFERENCES [dbo].[Surveys]
        ([Id])
    ON DELETE CASCADE ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_SurveyQuestion'
CREATE INDEX [IX_FK_SurveyQuestion]
ON [dbo].[Questions]
    ([SurveyId]);
GO

-- Creating foreign key on [OptionId] in table 'Answers'
ALTER TABLE [dbo].[Answers]
ADD CONSTRAINT [FK_OptionAnswer]
    FOREIGN KEY ([OptionId])
    REFERENCES [dbo].[Options]
        ([Id])
    ON DELETE CASCADE ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_OptionAnswer'
CREATE INDEX [IX_FK_OptionAnswer]
ON [dbo].[Answers]
    ([OptionId]);
GO

-- Creating foreign key on [ParticipatorId] in table 'Answers'
ALTER TABLE [dbo].[Answers]
ADD CONSTRAINT [FK_ParticipatorAnswer]
    FOREIGN KEY ([ParticipatorId])
    REFERENCES [dbo].[Participators]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ParticipatorAnswer'
CREATE INDEX [IX_FK_ParticipatorAnswer]
ON [dbo].[Answers]
    ([ParticipatorId]);
GO

-- Creating foreign key on [EditorId] in table 'Surveys'
ALTER TABLE [dbo].[Surveys]
ADD CONSTRAINT [FK_EditorSurvey]
    FOREIGN KEY ([EditorId])
    REFERENCES [dbo].[Editors]
        ([Id])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_EditorSurvey'
CREATE INDEX [IX_FK_EditorSurvey]
ON [dbo].[Surveys]
    ([EditorId]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------